﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace PhanCongGiangDay
{
    public class CHocPhan
    {
        public static List<MHocPhan> getHPByIdName(string idHP,string nameHP)
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            var hps = from hp in db.HOCPHANs
                      where hp.MAHP == idHP || hp.TENHP.Contains(nameHP)
                      select new MHocPhan
                      {
                          MAHP = hp.MAHP,
                          TENHP = hp.TENHP,
                          SOTC = Convert.ToInt32(hp.SOTC),
                          SOTIET =Convert.ToInt32(hp.SOTIET),
                          TENBM = hp.BOMON.TENBM
                      };
            return hps.ToList();
        }

        public static List<MHocPhan> getAll()
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            var hps = from hp in db.HOCPHANs
                      join bm in db.BOMONs
                      on hp.MABM equals bm.MABM                       
                      select new MHocPhan
                      {
                          MAHP = hp.MAHP,
                          TENHP = hp.TENHP,
                          SOTC = int.Parse(hp.SOTC.ToString()),
                          SOTIET = int.Parse(hp.SOTIET.ToString()),
                          TENBM = bm.TENBM
                      };
            return hps.ToList();
        }



        public static int addHP(string maHP, string tenHP, string maBM, string soTC, string soTiet)
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            try
            {
                HOCPHAN hp = new HOCPHAN
                {
                    MABM = maBM,
                    TENHP = tenHP,
                    SOTC = Convert.ToInt32(soTC),
                    SOTIET = Convert.ToInt32(soTiet),
                    MAHP = maHP
                };
                db.HOCPHANs.InsertOnSubmit(hp);
                db.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int updateHP(string maHP, string tenHP, string maBM, string soTC, string soTiet)
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            try
            {
                HOCPHAN hp = (from hpObject in db.HOCPHANs
                                where hpObject.MAHP == maHP
                                select hpObject).First();
                hp.TENHP = tenHP;
                hp.SOTIET = Convert.ToInt32(soTiet);
                hp.SOTC = Convert.ToInt32(soTC);
                hp.MABM = maBM;
                db.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int deleteHP(string maHP)
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            try
            {
                HOCPHAN hp = db.HOCPHANs.Where(h => h.MAHP == maHP).Single();
                db.HOCPHANs.DeleteOnSubmit(hp);
                db.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static object findHP(string maHK, string namHoc)
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            var hps = from hk in db.HOCKies
                      where hk.MAHK == maHK && hk.NAMHOC == namHoc
                      join kh in db.KEHOACHMONHOMs
                      on hk.MAHK equals kh.MAHK
                      join hp in db.HOCPHANs
                      on kh.MAHP equals hp.MAHP
                      join pc in db.PHANCONGGIANGDAYs
                      on kh.MAHP equals pc.MAHP where pc.MAHK == maHK
                      join gv in db.GIANGVIENs
                      on pc.MAGV equals gv.MAGV
                      join bm in db.BOMONs
                      on gv.MABM equals bm.MABM
                      select new
                      {
                          maHP = hp.MAHP,
                          tenHP = hp.TENHP,
                          soNhom = kh.SONHOM,
                          soTiet = hp.SOTIET,
                          soTC = hp.SOTC,         
                          tenBM= bm.TENBM,
                          tenGV = gv.HOTEN
                      };
            return hps.ToList();
            
        }

        public static List<MHocPhan> getBCHP(string namHoc)
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            var bchps = from hk in db.HOCKies
                        where hk.NAMHOC == namHoc
                        join kh in db.KEHOACHMONHOMs
                        on hk.MAHK equals kh.MAHK
                        join hp in db.HOCPHANs
                        on kh.MAHP equals hp.MAHP
                        select new MHocPhan
                        {
                            MAHP = hp.MAHP,
                            TENHP = hp.TENHP,
                            SOTC = Convert.ToInt32(hp.SOTC),
                            SOTIET = Convert.ToInt32(hp.SOTIET)
                        };
            return bchps.ToList();
        }

        public static DataSetKHML getKHNhomLop()
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            DataSetKHML dsNhomLop = new DataSetKHML();
            var hps = from kh in db.KEHOACHMONHOMs
                      join hp in db.HOCPHANs
                      on kh.MAHP equals hp.MAHP
                      select new
                      {
                          maHP = hp.MAHP,
                          tenHP = hp.TENHP,
                          soNhom = kh.SONHOM

                      };
            foreach (var item in hps.ToList())
            {
                DataRow row = dsNhomLop.Tables["KeHoachNhomLop"].NewRow();
                row["MaHP"] = item.maHP;
                row["TenHP"] = item.tenHP;
                row["SoNhom"] = item.soNhom;
                dsNhomLop.Tables["KeHoachNhomLop"].Rows.Add(row);
            }
            return dsNhomLop;
        }

        public static DataSetPC getPC()
        {
            PhanCongGiangDayDataContext db = new PhanCongGiangDayDataContext();
            DataSetPC dsPhanCong = new DataSetPC();
            var pcs = from pc in db.PHANCONGGIANGDAYs                     
                      select new
                      {
                          MaHP = pc.MAHP,
                          MaHK = pc.MAHK,
                          SoTTN = pc.SOTHUTUNHOM,
                          MaGV = pc.MAGV
                      };
            foreach (var item in pcs.ToList())
            {
                DataRow row = dsPhanCong.Tables["PhanCong"].NewRow();
                row["MaHP"] = item.MaHP;
                row["MaHK"] = item.MaHK;
                row["SoThuTuNhom"] = item.SoTTN;
                row["MaGV"] = item.MaGV;
                dsPhanCong.Tables["PhanCong"].Rows.Add(row);
            }
            return dsPhanCong;
        }
    }
}
